import pymysql
import pandas as pd
from Database import DatabaseHandler

db_handler = DatabaseHandler(host='localhost', port=3306, user='root', password='123456')
db_handler.create_database('test')
db_handler.cursor.execute('USE test')

# 创建表
sql = """  
create table if not exists school_fraction_line(  
    school varchar(225) ,  	
    region varchar(225),
    subject_classification varchar(225),	
    year varchar(225),	
    admission_batch	varchar(225),
    enrollment_type	varchar(225),
    minimum_score varchar(225),	
    minimum_position varchar(225),	
    provincial_control_line	varchar(225),
    professional_group varchar(225),
    course_selection_requirements varchar(225)
 )  
"""
db_handler.cursor.execute(sql)
print('表创建或已存在，SQL执行成功')

# 读取Excel文件
df = pd.read_excel(r"C:\Users\姚宇康\Desktop\日常文件\学习\学校分数线.xlsx")
df.fillna('', inplace=True)  # 填充空值

sql_insert = 'INSERT INTO school_fraction_line VALUES (%s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s)'
try:
    db_handler.cursor.executemany(sql_insert, df.to_records(index=False).tolist())
    print(db_handler.cursor.rowcount, "记录插入成功。")
except pymysql.MySQLError as e:
    print(f"数据库错误：{e}")
    # 注意：由于autocommit=True，通常不需要手动回滚

db_handler.close_connection()